
// ========== 实时投票系统后端 ===========
// 1. 依赖与初始化
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');
const cors = require('cors');
const bodyParser = require('body-parser');
const QRCode = require('qrcode');
const path = require('path');

const app = express();
const http = require('http').createServer(app);
const { Server } = require('socket.io');
const io = new Server(http, { cors: { origin: '*' } });
const PORT = 3001;
const JWT_SECRET = 'your_jwt_secret'; // 请替换为更安全的密钥

app.use(cors());
app.use(bodyParser.json());
// 托管 public 目录静态文件，并将首页设置为 scoreboard.html
// 首页特殊处理，显示投票列表
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'index.html'));
});
app.use(express.static(path.join(__dirname, 'public')));

// 2. 数据库初始化
const db = new sqlite3.Database('./voting.db');
db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE,
    password TEXT
  )`);
  db.run(`CREATE TABLE IF NOT EXISTS votes_meta (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    status TEXT,
    start TEXT,
    end TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);
  db.run(`CREATE TABLE IF NOT EXISTS options (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vote_id INTEGER,
    name TEXT
  )`);
  db.run(`CREATE TABLE IF NOT EXISTS votes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vote_id INTEGER,
    voter_id TEXT,
    option_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);
  db.run(`CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT
  )`);
  db.get('SELECT * FROM users WHERE username = ?', ['admin'], (err, row) => {
    if (!row) {
      bcrypt.hash('123456', 10, (err, hash) => {
        db.run('INSERT INTO users (username, password) VALUES (?, ?)', ['admin', hash]);
      });
    }
  });
});

// 2.1 简单迁移：为旧库补充缺失字段
function migrateSchema() {
  // options 表缺少 vote_id 列
  db.all('PRAGMA table_info(options)', (err, columns) => {
    if (Array.isArray(columns) && !columns.some(c => c.name === 'vote_id')) {
      db.run('ALTER TABLE options ADD COLUMN vote_id INTEGER', (e) => {
        if (e) console.error('迁移失败：options 添加 vote_id 列', e.message);
        else console.log('迁移完成：options 添加 vote_id 列');
      });
    }
  });
  // votes 表缺少 vote_id 列
  db.all('PRAGMA table_info(votes)', (err, columns) => {
    if (Array.isArray(columns) && !columns.some(c => c.name === 'vote_id')) {
      db.run('ALTER TABLE votes ADD COLUMN vote_id INTEGER', (e) => {
        if (e) console.error('迁移失败：votes 添加 vote_id 列', e.message);
        else console.log('迁移完成：votes 添加 vote_id 列');
      });
    }
  });
}

migrateSchema();

// 3. 工具/中间件
function auth(req, res, next) {
  const token = req.headers['authorization']?.split(' ')[1];
  if (!token) return res.status(401).json({ message: '未登录' });
  jwt.verify(token, JWT_SECRET, (err, decoded) => {
    if (err) return res.status(401).json({ message: '登录已过期' });
    req.user = decoded;
    next();
  });
}

// 4. 多场投票API（推荐使用）
// 新建投票场次
app.post('/api/votes', auth, (req, res) => {
  const { name, start, end, options } = req.body;
  // 默认直接开始，避免没有后台页面时阻塞
  db.run('INSERT INTO votes_meta (name, status, start, end) VALUES (?, ?, ?, ?)', [name, 'started', start, end], function(err) {
    if (err) return res.status(500).json({ message: '创建投票失败' });
    const vote_id = this.lastID;
    if (Array.isArray(options)) {
      const stmt = db.prepare('INSERT INTO options (vote_id, name) VALUES (?, ?)');
      options.forEach(opt => stmt.run(vote_id, opt));
      stmt.finalize();
    }
    res.json({ vote_id });
  });
});
// 获取所有投票场次列表
app.get('/api/votes', auth, (req, res) => {
  db.all('SELECT * FROM votes_meta ORDER BY id DESC', (err, rows) => {
    res.json(rows);
  });
});
// 获取单个投票场次详情
app.get('/api/votes/:voteId', auth, (req, res) => {
  const voteId = req.params.voteId;
  db.get('SELECT * FROM votes_meta WHERE id = ?', [voteId], (err, row) => {
    if (!row) return res.status(404).json({ message: '投票不存在' });
    db.all('SELECT * FROM options WHERE vote_id = ?', [voteId], (err, opts) => {
      row.options = opts;
      res.json(row);
    });
  });
});

// 公开：获取单个投票场次元信息（无需登录）
app.get('/api/public/votes/:voteId', (req, res) => {
  const voteId = req.params.voteId;
  db.get('SELECT id, name, status, start, end FROM votes_meta WHERE id = ?', [voteId], (err, row) => {
    if (err) return res.status(500).json({ message: '服务异常' });
    if (!row) return res.status(404).json({ message: '投票不存在' });
    res.json(row);
  });
});
// 更新投票场次（如开始、结束、修改时间/选项）
app.post('/api/votes/:voteId', auth, (req, res) => {
  const voteId = req.params.voteId;
  const { name, start, end, status, options } = req.body;
  db.run('UPDATE votes_meta SET name=?, start=?, end=?, status=? WHERE id=?', [name, start, end, status, voteId], function(err) {
    if (err) return res.status(500).json({ message: '更新失败' });
    if (Array.isArray(options)) {
      db.run('DELETE FROM options WHERE vote_id=?', [voteId], () => {
        const stmt = db.prepare('INSERT INTO options (vote_id, name) VALUES (?, ?)');
        options.forEach(opt => stmt.run(voteId, opt));
        stmt.finalize();
      });
    }
    res.json({ message: '更新成功' });
  });
});
// 获取某场投票的选项
app.get('/api/votes/:voteId/options', (req, res) => {
  const voteId = req.params.voteId;
  db.all('SELECT * FROM options WHERE vote_id = ?', [voteId], (err, rows) => {
    res.json(rows);
  });
});
// 获取某场投票的实时票数（公开）
app.get('/api/votes/:voteId/results', (req, res) => {
  const voteId = req.params.voteId;
  db.all('SELECT options.id, options.name, COUNT(votes.id) as count FROM options LEFT JOIN votes ON options.id = votes.option_id AND votes.vote_id = ? WHERE options.vote_id = ? GROUP BY options.id', [voteId, voteId], (err, rows) => {
    res.json(rows);
  });
});
// 投票（每人每场只能投一次）
app.post('/api/votes/:voteId/vote', (req, res) => {
  const voteId = req.params.voteId;
  const { voter_id, option_id } = req.body;
  db.get('SELECT * FROM votes_meta WHERE id = ?', [voteId], (err, meta) => {
    if (!meta) return res.status(404).json({ message: '投票不存在' });
    const now = Date.now();
    const withinTime = (!meta.start || now >= new Date(meta.start).getTime()) && (!meta.end || now <= new Date(meta.end).getTime());
    // 若未开始但时间允许，则自动置为 started
    if (meta.status !== 'started' && withinTime) {
      db.run('UPDATE votes_meta SET status = ? WHERE id = ?', ['started', voteId]);
      meta.status = 'started';
    }
    if (meta.status !== 'started') return res.status(403).json({ message: '投票未开始' });
    if (meta.start && now < new Date(meta.start).getTime()) return res.status(403).json({ message: '投票未开始' });
    if (meta.end && now > new Date(meta.end).getTime()) return res.status(403).json({ message: '投票已结束' });
    db.get('SELECT * FROM votes WHERE vote_id = ? AND voter_id = ?', [voteId, voter_id], (err, row) => {
      if (row) return res.status(403).json({ message: '每人只能投一票' });
      db.run('INSERT INTO votes (vote_id, voter_id, option_id) VALUES (?, ?, ?)', [voteId, voter_id, option_id], err => {
        if (err) return res.status(500).json({ message: '投票失败' });
        // 推送最新票数
        db.all('SELECT options.id, options.name, COUNT(votes.id) as count FROM options LEFT JOIN votes ON options.id = votes.option_id AND votes.vote_id = ? WHERE options.vote_id = ? GROUP BY options.id', [voteId, voteId], (err, rows) => {
          io.emit('voteUpdate_' + voteId, rows);
        });
        res.json({ message: '投票成功' });
      });
    });
  });
});
// 导出某场投票的所有投票记录
app.get('/api/votes/:voteId/export', auth, (req, res) => {
  const voteId = req.params.voteId;
  db.all('SELECT * FROM votes WHERE vote_id = ?', [voteId], (err, rows) => {
    res.json(rows);
  });
});

// 5. 兼容单场投票API（如需保留）
// 登录接口
app.post('/api/login', (req, res) => {
  const { username, password } = req.body;
  db.get('SELECT * FROM users WHERE username = ?', [username], (err, user) => {
    if (err || !user) return res.status(401).json({ message: '账号或密码错误' });
    bcrypt.compare(password, user.password, (err, result) => {
      if (result) {
        const token = jwt.sign({ username }, JWT_SECRET, { expiresIn: '2h' });
        res.json({ token });
      } else {
        res.status(401).json({ message: '账号或密码错误' });
      }
    });
  });
});
// 获取投票选项（全局）
app.get('/api/options', (req, res) => {
  db.all('SELECT * FROM options', (err, rows) => {
    res.json(rows);
  });
});
// 后台设置投票选项（全局）
app.post('/api/options', auth, (req, res) => {
  const { options } = req.body; // options: ["A", "B", ...]
  db.run('DELETE FROM options');
  const stmt = db.prepare('INSERT INTO options (name) VALUES (?)');
  options.forEach(opt => stmt.run(opt));
  stmt.finalize();
  res.json({ message: '设置成功' });
});
// 获取投票时间设置（全局）
app.get('/api/settings', (req, res) => {
  db.all('SELECT * FROM settings', (err, rows) => {
    const settings = {};
    rows.forEach(r => settings[r.key] = r.value);
    // 默认开启投票，避免前端一直显示“未开始”
    if (!('vote_status' in settings)) settings.vote_status = 'started';
    res.json(settings);
  });
});
// 后台设置投票时间和状态（全局）
app.post('/api/settings', auth, (req, res) => {
  const { start, end, status } = req.body;
  db.run('REPLACE INTO settings (key, value) VALUES (?, ?)', ['start', start]);
  db.run('REPLACE INTO settings (key, value) VALUES (?, ?)', ['end', end]);
  if (status) {
    db.run('REPLACE INTO settings (key, value) VALUES (?, ?)', ['vote_status', status]);
  }
  res.json({ message: '设置成功' });
});
// 投票接口（全局，每个voter_id只能投一次）
app.post('/api/vote', (req, res) => {
  const { voter_id, option_id } = req.body;
  db.all('SELECT * FROM settings', (err, rows) => {
    const settings = {};
    rows.forEach(r => settings[r.key] = r.value);
    const now = Date.now();
    // 默认视为已开始
    if ((settings.vote_status || 'started') !== 'started') {
      return res.status(403).json({ message: '投票未开始' });
    }
    if (settings.start && now < new Date(settings.start).getTime()) {
      return res.status(403).json({ message: '投票未开始' });
    }
    if (settings.end && now > new Date(settings.end).getTime()) {
      return res.status(403).json({ message: '投票已结束' });
    }
    db.get('SELECT * FROM votes WHERE voter_id = ?', [voter_id], (err, row) => {
      if (row) return res.status(403).json({ message: '每人只能投一票' });
      db.run('INSERT INTO votes (voter_id, option_id) VALUES (?, ?)', [voter_id, option_id], err => {
        if (err) return res.status(500).json({ message: '投票失败' });
        db.all('SELECT options.id, options.name, COUNT(votes.id) as count FROM options LEFT JOIN votes ON options.id = votes.option_id GROUP BY options.id', (err, rows) => {
          io.emit('voteUpdate', rows);
        });
        res.json({ message: '投票成功' });
      });
    });
  });
});
// 获取实时票数（全局）
app.get('/api/results', (req, res) => {
  db.all('SELECT options.id, options.name, COUNT(votes.id) as count FROM options LEFT JOIN votes ON options.id = votes.option_id GROUP BY options.id', (err, rows) => {
    res.json(rows);
  });
});
// 后台导出投票结果（全局）
app.get('/api/export', auth, (req, res) => {
  db.all('SELECT * FROM votes', (err, rows) => {
    res.json(rows);
  });
});

// 6. 其它API
// 生成投票二维码（返回base64图片）
app.get('/api/qrcode', (req, res) => {
  const voteUrl = req.query.url || 'http://localhost:3000/public/vote.html';
  QRCode.toDataURL(voteUrl, (err, url) => {
    res.json({ qrcode: url });
  });
});

// 7. socket.io 连接日志
io.on('connection', (socket) => {
  console.log('A client connected:', socket.id);
});

// 8. 启动服务
http.listen(PORT, () => {
  console.log(`Voting backend running at http://localhost:${PORT}`);
});
